﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace ContaTelefone.Data
{
    public class ReportsRepository
    {

        public DataTable getContaPorNumero(int pID_CONTA)
        {
            SqlConnection cn; SqlDataAdapter da;
            SqlCommand cmd;
            DataTable dt;

            try
            {
                cn = new SqlConnection(ConfigurationManager.ConnectionStrings["ContaTelefoneADODb"].ConnectionString);
                cn.Open();
                cmd = new SqlCommand();
                cmd.Connection = cn;
                cmd.CommandText = @"SELECT ID_CONTA,DATA_INICIO,DATA_FIM,CCUS_COBRAR,NUMACS,VALOR,ASSINATURA,DESCONTO,DADOS,BLACKBERRY,OUTROS
                                      FROM VW_VALOR_NUMERO
                                     WHERE ID_CONTA = @ID_CONTA";
                cmd.Parameters.AddWithValue("@ID_CONTA", pID_CONTA);
                da = new SqlDataAdapter(cmd);
                dt = new DataTable();
                da.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                da = null;
                cmd = null;
                cn = null;
            }
        }

        public DataTable getContaPorCCus(int pID_CONTA)
        {
            SqlConnection cn; SqlDataAdapter da;
            SqlCommand cmd;
            DataTable dt;

            try
            {
                cn = new SqlConnection(ConfigurationManager.ConnectionStrings["ContaTelefoneADODb"].ConnectionString);
                cn.Open();
                cmd = new SqlCommand();
                cmd.Connection = cn;
                cmd.CommandText = @"SELECT ID_CONTA,DATA_INICIO,DATA_FIM,CCUS_COBRAR,NUMACS,VALOR,Tipo
                                      FROM VW_VALOR_CCUS
                                     WHERE ID_CONTA = @ID_CONTA";
                cmd.Parameters.AddWithValue("@ID_CONTA", pID_CONTA);
                da = new SqlDataAdapter(cmd);
                dt = new DataTable();
                da.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                da = null;
                cmd = null;
                cn = null;
            }
        }

        public DataTable getConta(int pID_CONTA)
        {
            SqlConnection cn; SqlDataAdapter da;
            SqlCommand cmd;
            DataTable dt;

            try
            {
                cn = new SqlConnection(ConfigurationManager.ConnectionStrings["ContaTelefoneADODb"].ConnectionString);
                cn.Open();
                cmd = new SqlCommand();
                cmd.Connection = cn;
                cmd.CommandText = @"SELECT ID_CONTA,DATA_INICIO,DATA_FIM,NumAcs,Tpserv,Data,Origem,Destino,NumCham,Tipo,Duracao,Valor,NOME,EMAIL,ASSINATURA,DESCONTO,DADOS,BLACKBERRY,OUTROS,COBRAR,COD_CCUS,COD_CCUS_GERAL,CCUS_COBRAR,CCUS_COBRAR_ASSINATURA,CCUS_COBRAR_DADOS,CCUS_COBRAR_BLACKBERRY,CCUS_COBRAR_OUTROS,NOME_CLI_FOR,COBRAR_ASSINATURA,COBRAR_DADOS,COBRAR_BLACKBERRY,COBRAR_OUTROS
                                      FROM VW_CONTA
                                     WHERE ID_CONTA = @ID_CONTA";
                cmd.Parameters.AddWithValue("@ID_CONTA", pID_CONTA);
                da = new SqlDataAdapter(cmd);
                dt = new DataTable();
                da.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                da = null;
                cmd = null;
                cn = null;
            }
        }

        public DataTable getConta(int pID_CONTA, string pNUM_CEL)
        {
            SqlConnection cn; SqlDataAdapter da;
            SqlCommand cmd;
            DataTable dt;

            try
            {
                cn = new SqlConnection(ConfigurationManager.ConnectionStrings["ContaTelefoneADODb"].ConnectionString);
                cn.Open();
                cmd = new SqlCommand();
                cmd.Connection = cn;
                cmd.CommandText = @"SELECT ID_CONTA,DATA_INICIO,DATA_FIM,NumAcs,Tpserv,Data,Origem,Destino,NumCham,Tipo,Duracao,Valor,NOME,EMAIL,ASSINATURA,DESCONTO,DADOS,BLACKBERRY,OUTROS,COBRAR,COD_CCUS,COD_CCUS_GERAL,CCUS_COBRAR,CCUS_COBRAR_ASSINATURA,CCUS_COBRAR_DADOS,CCUS_COBRAR_BLACKBERRY,CCUS_COBRAR_OUTROS,NOME_CLI_FOR,COBRAR_ASSINATURA,COBRAR_DADOS,COBRAR_BLACKBERRY,COBRAR_OUTROS
                                      FROM VW_CONTA
                                     WHERE ID_CONTA = @ID_CONTA
                                       AND NumAcs = @NumAcs";
                cmd.Parameters.AddWithValue("@ID_CONTA", pID_CONTA);
                cmd.Parameters.AddWithValue("@NumAcs", pNUM_CEL);
                da = new SqlDataAdapter(cmd);
                dt = new DataTable();
                da.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                da = null;
                cmd = null;
                cn = null;
            }
        }
    }
}
